package com.example.mapp.buildSql;

import java.util.Map;

/**
 * Created by douyx on 2017/11/13.
 */
public class StatisticsMappSql {

	/**
	 * 辖区内电梯的总数量
	 * 
	 * @return
	 */
	public String countEleNum() {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT * FROM ");
		sb.append("(SELECT COUNT(1) elevatorNum FROM ELEVATOR WHERE IS_STOP IN ('0','1')) A,");
		sb.append("(SELECT COUNT(DISTINCT BRAND_NAME) brandNum FROM ELEVATOR) B,");
		sb.append("(SELECT COUNT(1) usedPartyNum FROM PARTY WHERE PARTY_TYPE = '2') C,");
		sb.append("(SELECT COUNT(1) maintPartyNum FROM PARTY WHERE PARTY_TYPE = '1') D,");
		sb.append("(SELECT COUNT(DISTINCT CONTRACT_NAME) contractNum FROM PARTY_CONTRACT) E,");
		sb.append("(SELECT COUNT(1) operatorNum FROM SYS_USER_ROLE WHERE (ROLE_ID = '5' OR ROLE_ID = '6' OR ROLE_ID = '7' OR ROLE_ID = '8')) F, ");
		sb.append(" (select sum(case when place_type = '重点场所' then 1 else 0 end) as importantElevatorCount from elevator WHERE IS_STOP IN ('0','1') ) H , ");
		sb.append(" (select sum(case when place_type != '重点场所' OR place_type IS NULL then 1 else 0 end) as unimportantElevatorCount from elevator WHERE IS_STOP IN ('0','1') ) I ");
		return sb.toString();
	}

	/**
	 * 辖区内电梯数量分布
	 * 
	 * @param inMap
	 * @return
	 */
	public String countNumDistrib(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select a.* from ( ");
		sb.append("SELECT A.ID id,A.PARTY_TYPE partyType,A.PARTY_NAME partyName,A.last_manage_party lastManageParty,IFNULL(SUM(C.elevatorNum),0) elevatorNum,");
		sb.append("IFNULL(SUM(C.eleUseNum),0) eleUsedNum,IFNULL(SUM(C.eleStopNum),0) eleStopNum FROM ");
		sb.append("PARTY A LEFT JOIN SUB_PARTY_INFO B ON A.ID = B.PARENT_ID ");
		sb.append("LEFT JOIN (SELECT SUM(CASE WHEN IS_STOP IN ('0','1') THEN 1 ELSE 0 END) elevatorNum,");
		sb.append("SUM(CASE WHEN IS_STOP = '0' THEN 1 ELSE 0 END) eleUseNum,");
		sb.append("SUM(CASE WHEN IS_STOP = '1' THEN 1 ELSE 0 END) eleStopNum,");
		sb.append("USED_PARTY_ID FROM ELEVATOR ");
		sb.append("GROUP BY USED_PARTY_ID) C ON B.ID = C.USED_PARTY_ID ");
		sb.append("WHERE A.PARENT_ID = #{id} AND A.PARENT_ID != A.ID ");
		sb.append("GROUP BY A.ID,A.PARTY_NAME,A.last_manage_party ");
		sb.append(" ) a where a.partyType=4 ");
		return sb.toString();
	}

	/**
	 * 辖区内电梯类型分布
	 * 
	 * @return
	 */
	public String countTypeDistrib() {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT a.elevator_type_id typeId,COUNT(1) elevatorNum FROM elevator a ");
		sb.append("LEFT JOIN elevator_type b ON a.elevator_type_id=b.id GROUP BY a.elevator_type_id ");
		return sb.toString();
	}

	/**
	 * 辖区内电梯年限分布
	 * 
	 * @return
	 */
	public String countYearDistrib() {
		StringBuffer sb = new StringBuffer();
		sb.append(
				"SELECT SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 < 10 THEN 1 ELSE 0 END) lessTen,");
		sb.append(
				"SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 >= 10 AND (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 < 15 THEN 1 ELSE 0 END) lessFifteen,");
		sb.append(
				"SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 >= 15 AND (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 < 20 THEN 1 ELSE 0 END) lessTwenty,");
		sb.append(
				"SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 >= 20 THEN 1 ELSE 0 END) gatherTwenty FROM ELEVATOR ");
		return sb.toString();
	}

	/**
	 * 辖区内保障力量分布
	 * 
	 * @return
	 */
	public String countOperDistrib() {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT SUM(CASE WHEN ROLE_ID = 3 THEN 1 ELSE 0 END) watcherNum,");
		sb.append("SUM(CASE WHEN ROLE_ID = 5 THEN 1 ELSE 0 END) saferNum,");
		sb.append("SUM(CASE WHEN ROLE_ID = 7 THEN 1 ELSE 0 END) mainterNum ");
		sb.append("FROM SYS_USER_ROLE ");
		return sb.toString();
	}
	
	/**
	 * 辖区内各项安全状况统计
	 * 
	 * @return
	 */
	public String countSafeNum() {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT * FROM ");
		sb.append("(SELECT COUNT(1) dayFeedback FROM ELEVATOR_APPRAISAL WHERE (EVALUATE_TYPE = '1' OR (EVALUATE_TYPE = '2' AND IS_LIKE = '1')) AND APPRAISAL_TIME LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')) A, ");
		sb.append("(SELECT COUNT(1) quarterFeedback FROM ELEVATOR_APPRAISAL WHERE (EVALUATE_TYPE = '1' OR (EVALUATE_TYPE = '2' AND IS_LIKE = '1')) AND QUARTER(APPRAISAL_TIME) = QUARTER(NOW()) AND YEAR(APPRAISAL_TIME) = YEAR(NOW())) B,");
		sb.append("(SELECT COUNT(1) dayRescue FROM EMERGENCY_RESCUE WHERE RESCUE_END_TIME LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')) C,");
		sb.append("(SELECT COUNT(1) quarterRescue FROM EMERGENCY_RESCUE WHERE QUARTER(RESCUE_END_TIME) = QUARTER(NOW()) AND YEAR(RESCUE_END_TIME) = YEAR(NOW())) D ");
		return sb.toString();
	}
	
	/**
	 * 电梯安全保障工作落实情况
	 * 
	 * @return
	 */
	public String countMaintRepairNum() {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT A.DEBRIS_DAY_NUM + B.ESCALATOR_DAY_NUM + C.HYDPRESS_DAY_NUM + D.TRACTION_DAY_NUM dayMaintNum,");
		sb.append("E.DEBRIS_QUARTER_NUM + F.ESCALATOR_QUARTER_NUM + G.HYDPRESS_QUARTER_NUM + H.TRACTION_QUARTER_NUM quarterMaintNum,");
		sb.append("J.DEBRIS_YEAR_NUM + K.ESCALATOR_YEAR_NUM + L.HYDPRESS_YEAR_NUM + M.TRACTION_YEAR_NUM yaerMaintNum,");
		sb.append("N.dayInspNum,O.quarterInspNum,P.yearInspNum FROM ");
		sb.append("(SELECT COUNT(1) DEBRIS_DAY_NUM FROM DEBRIS_HALF_MON WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')) A,");
		sb.append("(SELECT COUNT(1) ESCALATOR_DAY_NUM FROM ESCALATOR_HALF_MON WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')) B,");
		sb.append("(SELECT COUNT(1) HYDPRESS_DAY_NUM FROM HYDPRESS_HALF_MON WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')) C,");
		sb.append("(SELECT COUNT(1) TRACTION_DAY_NUM FROM TRACTION_HALF_MON WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')) D,");
		sb.append("(SELECT COUNT(1) DEBRIS_QUARTER_NUM FROM DEBRIS_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())) E,");
		sb.append("(SELECT COUNT(1) ESCALATOR_QUARTER_NUM FROM ESCALATOR_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())) F,");
		sb.append("(SELECT COUNT(1) HYDPRESS_QUARTER_NUM FROM HYDPRESS_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())) G,");
		sb.append("(SELECT COUNT(1) TRACTION_QUARTER_NUM FROM TRACTION_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())) H,");
		sb.append("(SELECT COUNT(1) DEBRIS_YEAR_NUM FROM DEBRIS_HALF_MON WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())) J,");
		sb.append("(SELECT COUNT(1) ESCALATOR_YEAR_NUM FROM ESCALATOR_HALF_MON WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())) K,");
		sb.append("(SELECT COUNT(1) HYDPRESS_YEAR_NUM FROM HYDPRESS_HALF_MON WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())) L,");
		sb.append("(SELECT COUNT(1) TRACTION_YEAR_NUM FROM TRACTION_HALF_MON WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())) M,");
		sb.append("(SELECT COUNT(1) dayInspNum FROM DAILY_INSPECTION WHERE CHECK_END_DT LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')) N,");
		sb.append("(SELECT COUNT(1) quarterInspNum FROM DAILY_INSPECTION WHERE QUARTER(CHECK_END_DT) = QUARTER(NOW()) AND YEAR(CHECK_END_DT) = YEAR(NOW())) O,");
		sb.append("(SELECT COUNT(1) yearInspNum FROM DAILY_INSPECTION WHERE YEAR(CHECK_END_DT) = YEAR(NOW())) P");
		return sb.toString();
	}
	
	/**
	 * 电梯事故隐患排除情况
	 * 
	 * @param inMap
	 * @return
	 */
	public String countRepairNum(Map inMap) {
		StringBuffer sb = new StringBuffer();
        sb.append(" select a.* from ( ");
		sb.append("SELECT A.ID id,A.party_type partyType,A.PARTY_NAME partyName,A.last_manage_party lastManageParty,IFNULL(SUM(C.NUM),0) dayRepairNum,");
		sb.append("IFNULL(SUM(D.NUM),0) quarterRepairNum,IFNULL(SUM(E.NUM),0) yearRepairNum FROM ");
		sb.append("PARTY A LEFT JOIN SUB_PARTY_INFO B ON A.ID = B.PARENT_ID ");
		sb.append("LEFT JOIN (SELECT COUNT(1) NUM,REPAIR_PARTY_ID FROM REPAIR_INFO WHERE RESOLVE_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%') GROUP BY REPAIR_PARTY_ID) C ON B.ID = C.REPAIR_PARTY_ID ");
		sb.append("LEFT JOIN (SELECT COUNT(1) NUM,REPAIR_PARTY_ID FROM REPAIR_INFO WHERE QUARTER(RESOLVE_DATE) = QUARTER(NOW()) AND YEAR(RESOLVE_DATE) = YEAR(NOW()) GROUP BY REPAIR_PARTY_ID) D ON B.ID = D.REPAIR_PARTY_ID ");
		sb.append("LEFT JOIN (SELECT COUNT(1) NUM,REPAIR_PARTY_ID FROM REPAIR_INFO WHERE YEAR(RESOLVE_DATE) = YEAR(NOW()) GROUP BY REPAIR_PARTY_ID) E ON B.ID = E.REPAIR_PARTY_ID ");
		sb.append("WHERE A.PARENT_ID = #{id} AND A.PARENT_ID != A.ID ");
		sb.append("GROUP BY A.ID,A.PARTY_NAME,A.last_manage_party ");
        sb.append(" ) a where a.partyType='4' ");
		return sb.toString();
	}
	
	/**
	 * 电梯安全保障工作超期情况
	 * 
	 * @param inMap
	 * @return
	 */
	public String countMaintNum(Map inMap) {
		StringBuffer sb = new StringBuffer();
        sb.append(" select a.* from ( ");
		sb.append("SELECT A.ID id,A.party_type partyType,A.PARTY_NAME partyName,A.last_manage_party lastManageParty,IFNULL(SUM(C.NUM),0) dayMaintNum,");
		sb.append("IFNULL(SUM(D.NUM),0) quarterMaintNum,IFNULL(SUM(E.NUM),0) yearMaintNum,IFNULL(SUM(F.NUM),0) dayCheckNum,");
		sb.append("IFNULL(SUM(G.NUM),0) quarterCheckNum,IFNULL(SUM(H.NUM),0) yearCheckNum FROM ");
		sb.append("PARTY A LEFT JOIN SUB_PARTY_INFO B ON A.ID = B.PARENT_ID ");
		sb.append("LEFT JOIN (SELECT A.MAINTENANCE_PARTY_ID,IFNULL(SUM(A.NUM),0) NUM FROM ");
		sb.append("(SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM DEBRIS_HALF_MON WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%') AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL ");
		sb.append("SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM ESCALATOR_HALF_MON WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%') AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL ");
		sb.append("SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM HYDPRESS_HALF_MON WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%') AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL ");
		sb.append("SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM TRACTION_HALF_MON WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%') AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID) A GROUP BY A.MAINTENANCE_PARTY_ID) C ON B.ID = C.MAINTENANCE_PARTY_ID ");
		sb.append("LEFT JOIN (SELECT A.MAINTENANCE_PARTY_ID,IFNULL(SUM(A.NUM),0) NUM FROM ");
		sb.append("(SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM DEBRIS_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND YEAR(MAINTENANCE_END_DATE) = YEAR(NOW()) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL ");
		sb.append("SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM ESCALATOR_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND YEAR(MAINTENANCE_END_DATE) = YEAR(NOW()) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL ");
		sb.append("SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM HYDPRESS_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND YEAR(MAINTENANCE_END_DATE) = YEAR(NOW()) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL ");
		sb.append("SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM TRACTION_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND YEAR(MAINTENANCE_END_DATE) = YEAR(NOW()) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID) A GROUP BY A.MAINTENANCE_PARTY_ID) D ON B.ID = D.MAINTENANCE_PARTY_ID ");
		sb.append("LEFT JOIN (SELECT A.MAINTENANCE_PARTY_ID,IFNULL(SUM(A.NUM),0) NUM FROM ");
		sb.append("(SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM DEBRIS_HALF_MON WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW()) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL ");
		sb.append("SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM ESCALATOR_HALF_MON WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW()) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL ");
		sb.append("SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM HYDPRESS_HALF_MON WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW()) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL ");
		sb.append("SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM TRACTION_HALF_MON WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW()) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID) A GROUP BY A.MAINTENANCE_PARTY_ID) E ON B.ID = E.MAINTENANCE_PARTY_ID ");
		sb.append("LEFT JOIN (SELECT COUNT(1) NUM,B.MAINTENANCE_PARTY_ID FROM ELEVATOR_OVERTIME A LEFT JOIN ELEVATOR B ON A.ELEVATOR_ID = B.ID WHERE A.OVERTIME_DATE = DATE_FORMAT(NOW(),'%Y%m%d') GROUP BY B.MAINTENANCE_PARTY_ID) F ON B.ID = F.MAINTENANCE_PARTY_ID ");
		sb.append("LEFT JOIN (SELECT COUNT(1) NUM,B.MAINTENANCE_PARTY_ID FROM ELEVATOR_OVERTIME A LEFT JOIN ELEVATOR B ON A.ELEVATOR_ID = B.ID WHERE QUARTER(A.OVERTIME_DATE) = QUARTER(NOW()) AND YEAR(A.OVERTIME_DATE) = YEAR(NOW()) GROUP BY B.MAINTENANCE_PARTY_ID) G ON B.ID = G.MAINTENANCE_PARTY_ID ");
		sb.append("LEFT JOIN (SELECT COUNT(1) NUM,B.MAINTENANCE_PARTY_ID FROM ELEVATOR_OVERTIME A LEFT JOIN ELEVATOR B ON A.ELEVATOR_ID = B.ID WHERE YEAR(A.OVERTIME_DATE) = YEAR(NOW()) GROUP BY B.MAINTENANCE_PARTY_ID) H ON B.ID = H.MAINTENANCE_PARTY_ID ");
		sb.append("WHERE  A.PARENT_ID != A.ID ");
		sb.append("GROUP BY A.ID,A.PARTY_NAME,A.last_manage_party ");
        sb.append(" ) a where a.partyType='4' and a.id in (select id from party where id in (select id from party where parent_id=#{id}))  ");
		return sb.toString();
	}

	/**
	 * 辖区内电梯的总数量(物业)
	 * 
	 * @param inMap
	 * @return
	 */
	public String countElevatorNum(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(a2.elevator_num),0) total_count ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1 ");
		sb.append(
				"LEFT JOIN (SELECT used_party_id,COUNT(1) elevator_num FROM elevator GROUP BY used_party_id) a2 ON a1.id = a2.used_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type ");

		return sb.toString();
	}

	/**
	 * 辖区内电梯的总数量(维保)
	 * 
	 * @param inMap
	 * @return
	 */
	public String countEleMaintNum(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(a2.elevator_num),0) total_count ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1 ");
		sb.append(
				"LEFT JOIN (SELECT maintenance_party_id,COUNT(1) elevator_num FROM elevator GROUP BY maintenance_party_id) a2 ON a1.id = a2.maintenance_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type ");

		return sb.toString();
	}

	/**
	 * 不同类型电梯数量占比
	 * 
	 * @param inMap
	 * @return
	 */
	public String countEleTypeNum(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,a2.elevator_type_id,IFNULL(SUM(a2.elevator_num),0) total_count ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1 ");
		sb.append("LEFT JOIN ( ");
		sb.append("SELECT a.used_party_id,a.elevator_type_id,COUNT(1) elevator_num FROM elevator a ");
		sb.append("LEFT JOIN elevator_type b ON a.elevator_type_id=b.id GROUP BY a.used_party_id,a.elevator_type_id ");
		sb.append(") a2 ON a1.id = a2.used_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type,a2.elevator_type_id ");

		return sb.toString();
	}

	/**
	 * 使用年限达10年以上电梯数量
	 * 
	 * @param inMap
	 * @return
	 */
	public String countEleLifeNumOlder(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(a2.elevator_num),0) total_count ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1 ");
		sb.append(
				"LEFT JOIN (SELECT used_party_id,COUNT(1) elevator_num FROM elevator where (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 >= 10 GROUP BY used_party_id) a2 ON a1.id = a2.used_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type ");

		return sb.toString();
	}

	/**
	 * 使用年限达10年以下电梯数量
	 * 
	 * @param inMap
	 * @return
	 */
	public String countEleLifeNumYounger(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(a2.elevator_num),0) total_count ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1 ");
		sb.append(
				"LEFT JOIN (SELECT used_party_id,COUNT(1) elevator_num FROM elevator where (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 < 10 GROUP BY used_party_id) a2 ON a1.id = a2.used_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type ");

		return sb.toString();
	}

	/**
	 * 本帐户辖区维保单位数量
	 * 
	 * @param inMap
	 * @return
	 */
	public String countMaintCompany(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(CASE WHEN a1.party_type = '1' THEN 1 ELSE 0 END),0) total_count ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1 ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type ");

		return sb.toString();
	}

	/**
	 * 本帐户辖区使用单位数量
	 * 
	 * @param inMap
	 * @return
	 */
	public String countUseCompany(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(CASE WHEN a1.party_type = '2' THEN 1 ELSE 0 END),0) total_count ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1 ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type ");

		return sb.toString();
	}

	/**
	 * 各单位 资质 数量 及 占比
	 * 
	 * @param inMap
	 * @return
	 */
	public String countCompanyQuali(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"DISTINCT a1.id,a1.party_name,a1.party_type,IFNULL(a1.holders_num,'0') holders_num,IFNULL(a1.license_ratio,'0') license_ratio ");
		sb.append("FROM  ");
		sb.append("party a1 ");
		sb.append("WHERE (a1.id = #{id} OR a1.parent_id = #{id}) ");

		return sb.toString();
	}

	/**
	 * 各星级 维保单位总数及占比
	 * 
	 * @param inMap
	 * @return
	 */
	public String countStarCompany(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type, a2.start_num,IFNULL(SUM(a2.party_num),0) total_count ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1 ");
		sb.append(
				"LEFT JOIN (SELECT party_id,start_num,COUNT(1) party_num FROM maintenance_party_detail GROUP BY party_id,start_num) a2 ON a1.id = a2.party_id ");
		sb.append(
				"WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) and a1.party_type='1' ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type,a2.start_num ");

		return sb.toString();
	}

	/**
	 * 本帐户辖区安全责任主体单位各类型技术人员持证数量
	 * 
	 * @param inMap
	 * @return
	 */
	public String countTechmanCompany(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(a2.used_czry_num),0) used_czry_num,IFNULL(SUM(a3.maint_czry_num),0) maint_czry_num ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1 ");
		sb.append(
				"LEFT JOIN (SELECT party_id,czry_num used_czry_num FROM used_party_detail) a2 ON a1.id = a2.party_id ");
		sb.append(
				"LEFT JOIN (select party_id, certified_staff_num maint_czry_num from maintenance_party_detail) a3 ON a1.id = a3.party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type ");

		return sb.toString();
	}

	/**
	 * 乘客投诉处理的反馈 率
	 * 
	 * @param inMap
	 * @return
	 */
	public String countFeedbackRio(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append("a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type, ");
		sb.append(
				"CASE WHEN IFNULL(SUM(a2.feed_count),0) = 0 THEN 0 ELSE IFNULL(SUM(feed_count),0)/IFNULL(SUM(eva_count),0) END feedback_rio ");
		sb.append("FROM   ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1  ");
		sb.append(
				"LEFT JOIN (SELECT a2.used_party_id,IFNULL(SUM(eva_count),0) eva_count,IFNULL(SUM(feed_count),0) feed_count FROM elevator a2 ");
		sb.append(
				"LEFT JOIN (SELECT elevator_id,COUNT(1) eva_count FROM elevator_appraisal WHERE evaluate_type = '2' GROUP BY elevator_id) a3 ON a2.id=a3.elevator_id ");
		sb.append(
				"LEFT JOIN (SELECT elevator_id,COUNT(1) feed_count FROM elevator_appraisal WHERE evaluate_type = '2' AND is_feed_back='1' GROUP BY elevator_id) a4 ON a2.id=a4.elevator_id ");
		sb.append("GROUP BY a2.used_party_id) a2  ");
		sb.append("ON a1.id = a2.used_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})  ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type  ");

		return sb.toString();
	}

	/**
	 * 本帐户辖区整体维保准时率
	 * 
	 * @param inMap
	 * @return
	 */
	public String countMaintInTime(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append("a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type, ");
		sb.append(
				"CASE WHEN IFNULL(SUM(a2.maint_in_time_count),0) = 0 THEN 0 ELSE IFNULL(SUM(maint_in_time_count),0)/IFNULL(SUM(maint_count),0) END maint_rio ");
		sb.append("FROM   ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id where a1.party_type != '1') a1  ");
		sb.append(
				"LEFT JOIN (SELECT a2.used_party_id,IFNULL(SUM(maint_in_time_count),0) maint_in_time_count,IFNULL(SUM(maint_count),0) maint_count FROM elevator a2 ");
		sb.append("INNER JOIN (SELECT elevator_num,IFNULL(SUM(maint_in_time_count),0) maint_in_time_count FROM  ");
		sb.append(
				"(SELECT elevator_num,COUNT(1) maint_in_time_count FROM debris_half_mon WHERE is_in_time_maint='1' GROUP BY elevator_num ");
		sb.append(
				"UNION SELECT elevator_num,COUNT(1) maint_in_time_count FROM escalator_half_mon WHERE is_in_time_maint='1' GROUP BY elevator_num ");
		sb.append(
				"UNION SELECT elevator_num,COUNT(1) maint_in_time_count FROM hydpress_half_mon WHERE is_in_time_maint='1' GROUP BY elevator_num ");
		sb.append(
				"UNION SELECT elevator_num,COUNT(1) maint_in_time_count FROM traction_half_mon WHERE is_in_time_maint='1' GROUP BY elevator_num) a GROUP BY elevator_num) a3 ON a2.elevator_num=a3.elevator_num ");
		sb.append("INNER JOIN (SELECT elevator_num,IFNULL(SUM(maint_count),0) maint_count FROM  ");
		sb.append(
				"(SELECT elevator_num,COUNT(1) maint_count FROM debris_half_mon WHERE is_in_time_maint IN( '1','2') GROUP BY elevator_num ");
		sb.append(
				"UNION SELECT elevator_num,COUNT(1) maint_count FROM escalator_half_mon WHERE is_in_time_maint IN( '1','2') GROUP BY elevator_num ");
		sb.append(
				"UNION SELECT elevator_num,COUNT(1) maint_count FROM hydpress_half_mon WHERE is_in_time_maint IN( '1','2') GROUP BY elevator_num ");
		sb.append(
				"UNION SELECT elevator_num,COUNT(1) maint_count FROM traction_half_mon WHERE is_in_time_maint IN( '1','2') GROUP BY elevator_num) a GROUP BY elevator_num) a4 ON a2.elevator_num=a4.elevator_num ");
		sb.append("GROUP BY a2.used_party_id) a2  ");
		sb.append("ON a1.id = a2.used_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})  ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type  ");

		return sb.toString();
	}

	/**
	 * 各类型周期性维保的平均时长
	 * 
	 * @param inMap
	 * @return
	 */
	public String countTypeMaintTime(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a2.type,a1.parent_type party_type,IFNULL(SUM(a2.maint_used_time),0)/COUNT(1) maint_used_time ");
		sb.append("FROM   ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id where a1.party_type != '1') a1  ");
		sb.append(
				"LEFT JOIN (SELECT a2.used_party_id,a3.type,IFNULL(SUM(maint_used_time),0)/COUNT(1) maint_used_time FROM elevator a2 ");
		sb.append(
				"INNER JOIN (SELECT type,elevator_num,SUM(maintenancen_used_time)/COUNT(1) maint_used_time FROM debris_half_mon WHERE maintenancen_used_time IS NOT NULL GROUP BY elevator_num,type ");
		sb.append(
				"UNION SELECT type,elevator_num,SUM(maintenancen_used_time)/COUNT(1) maint_used_time FROM escalator_half_mon WHERE maintenancen_used_time IS NOT NULL GROUP BY elevator_num,type ");
		sb.append(
				"UNION SELECT type,elevator_num,SUM(maintenancen_used_time)/COUNT(1) maint_used_time FROM hydpress_half_mon WHERE maintenancen_used_time IS NOT NULL GROUP BY elevator_num,type ");
		sb.append(
				"UNION SELECT type,elevator_num,SUM(maintenancen_used_time)/COUNT(1) maint_used_time FROM traction_half_mon WHERE maintenancen_used_time IS NOT NULL GROUP BY elevator_num,type) a3 ON a2.elevator_num=a3.elevator_num ");
		sb.append("GROUP BY a2.used_party_id,a3.type) a2  ");
		sb.append("ON a1.id = a2.used_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})  ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type,a2.type ");

		return sb.toString();
	}

	/**
	 * (按月)本帐户辖区周期内整体召修/维修落实率
	 * 
	 * @param inMap
	 * @return
	 */
	public String countRepair(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT   ");
		sb.append("a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,  ");
		sb.append(
				"CASE WHEN IFNULL(SUM(a2.repaird_count),0) = 0 THEN 0 ELSE IFNULL(SUM(repaird_count),0)/IFNULL(SUM(all_count),0) END repaired_rio  ");
		sb.append("FROM    ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id where a1.party_type != '1' and a1.party_type != '3') a1    ");
		sb.append(
				"LEFT JOIN (SELECT a2.used_party_id,IFNULL(SUM(repaird_count),0) repaird_count,IFNULL(SUM(all_count),0) all_count FROM elevator a2  ");
		sb.append(
				"LEFT JOIN (SELECT elevator_id,COUNT(1) repaird_count FROM repair_info WHERE is_repaired = '1' AND SUBSTRING(report_date,1,6) = #{reportDate} GROUP BY elevator_id) a3 ON a2.id=a3.elevator_id  ");
		sb.append(
				"LEFT JOIN (SELECT elevator_id,COUNT(1) all_count FROM repair_info WHERE is_repaired IN ('1','0') AND SUBSTRING(report_date,1,6) = #{reportDate} GROUP BY elevator_id) a4 ON a2.id=a4.elevator_id  ");
		sb.append("GROUP BY a2.used_party_id) a2   ");
		sb.append("ON a1.id = a2.used_party_id  ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})   ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type   ");

		return sb.toString();
	}

	/**
	 * 本帐户辖区周期内现场应急救援累计数
	 * 
	 * @param inMap
	 * @return
	 */
	public String countRescue(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT   ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,SUM(rescue_count) rescue_count ");
		sb.append("FROM    ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1   ");
		sb.append("LEFT JOIN (SELECT a2.used_party_id,IFNULL(SUM(rescue_count),0) rescue_count FROM elevator a2  ");
		sb.append(
				"LEFT JOIN (SELECT elevator_id,COUNT(1) rescue_count FROM emergency_rescue where locate(DATE_FORMAT(NOW(),'%Y'),rescue_end_time)>0 GROUP BY elevator_id) a3 ON a2.id=a3.elevator_id  ");
		sb.append("GROUP BY a2.used_party_id) a2   ");
		sb.append("ON a1.id = a2.used_party_id  ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})   ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type  ");

		return sb.toString();
	}

	/**
	 * 使用单位（物业）应急救援演练总次数
	 * 
	 * @param inMap
	 * @return
	 */
	public String countRescueDrill(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(a2.drill_count),0) drill_count ");
		sb.append("FROM   ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id where a1.party_type != '1' and a1.party_type != '3') a1  ");
		sb.append(
				"LEFT JOIN (SELECT drill_party_id,COUNT(1) drill_count FROM rescue_drill where locate(DATE_FORMAT(NOW(),'%Y'),drill_date) > 0 GROUP BY drill_party_id) a2 ON a1.id=a2.drill_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})   ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type  ");

		return sb.toString();
	}

	/**
	 * 检验机构数量
	 * 
	 * @param inMap
	 * @return
	 */
	public String countCheckOrgNum(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(a2.check_org_num),0) check_org_num ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1  ");
		sb.append("LEFT JOIN (select id, check_org_num from party) a2 ON a1.id=a2.id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})   ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type  ");

		return sb.toString();
	}

	/**
	 * 人员数量
	 * 
	 * @param inMap
	 * @return
	 */
	public String countUserNum(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(a2.user_num),0) user_num ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1  ");
		sb.append(
				"LEFT JOIN (select party_id, count(1) user_num from party_user group by party_id) a2 ON a1.id=a2.party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})   ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type  ");

		return sb.toString();
	}

	/**
	 * 使用单位（物业）过期电梯数
	 * 
	 * @param inMap
	 * @return
	 */
	public String countOvertimeElevator(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(a2.over_ele_count),0) over_ele_count,IFNULL(SUM(a3.ele_count),0) ele_count, ");
		sb.append(
				"CASE WHEN IFNULL(SUM(a2.over_ele_count),0) = 0 THEN 0 ELSE IFNULL(SUM(a2.over_ele_count),0)/IFNULL(SUM(a3.ele_count),0) END over_rio ");
		sb.append("FROM   ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id where a1.party_type != '1') a1  ");
		sb.append(
				"LEFT JOIN (SELECT used_party_id,COUNT(1) over_ele_count FROM elevator where next_check_date < DATE_FORMAT(NOW(),'%Y%m%d') GROUP BY used_party_id) a2 ON a1.id=a2.used_party_id ");
		sb.append(
				"LEFT JOIN (SELECT used_party_id,COUNT(1) ele_count FROM elevator GROUP BY used_party_id) a3 ON a1.id=a3.used_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})   ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type  ");

		return sb.toString();
	}

	/**
	 * 年度乘客投诉处理的反馈 率
	 * 
	 * @param inMap
	 * @return
	 */
	public String countFeedbackYear(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,IFNULL(SUM(feed_count),0) feed_count,IFNULL(SUM(eva_count),0) eva_count, ");
		sb.append(
				"CASE WHEN IFNULL(SUM(a2.feed_count),0) = 0 THEN 0 ELSE IFNULL(SUM(feed_count),0)/IFNULL(SUM(eva_count),0) END feedback_rio ");
		sb.append("FROM   ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1  ");
		sb.append(
				"LEFT JOIN (SELECT a2.used_party_id,IFNULL(SUM(eva_count),0) eva_count,IFNULL(SUM(feed_count),0) feed_count FROM elevator a2 ");
		sb.append(
				"LEFT JOIN (SELECT elevator_id,COUNT(1) eva_count FROM elevator_appraisal WHERE evaluate_type = '2' and LOCATE(DATE_FORMAT(NOW(),'%Y'),appraisal_time) > 0 GROUP BY elevator_id) a3 ON a2.id=a3.elevator_id ");
		sb.append(
				"LEFT JOIN (SELECT elevator_id,COUNT(1) feed_count FROM elevator_appraisal WHERE evaluate_type = '2' AND is_feed_back='1' and LOCATE(DATE_FORMAT(NOW(),'%Y'),appraisal_time) > 0 GROUP BY elevator_id) a4 ON a2.id=a4.elevator_id ");
		sb.append("GROUP BY a2.used_party_id) a2  ");
		sb.append("ON a1.id = a2.used_party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})  ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type  ");

		return sb.toString();
	}

	/**
	 * 本区域 使用单位各资质数量，汇总统计。
	 * 
	 * @param inMap
	 * @return
	 */
	public String countUsedZz(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,a2.zzjb,IFNULL(SUM(a2.zzjb_count),0) zzjb_count ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id where a1.party_type != '1') a1 ");
		sb.append(
				"LEFT JOIN (SELECT party_id,zzjb,count(1) zzjb_count FROM used_party_detail group by party_id,zzjb) a2 ON a1.id = a2.party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type,a2.zzjb ");

		return sb.toString();
	}

	/**
	 * 本区域 维保单位各资质数量，汇总统计。
	 * 
	 * @param inMap
	 * @return
	 */
	public String countMaintZz(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,a3.zzjb,IFNULL(SUM(a3.zzjb_count),0) zzjb_count ");
		sb.append("FROM  ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id where a1.party_type != '2') a1 ");
		sb.append(
				"LEFT JOIN (select party_id, zzjb, count(1) zzjb_count from maintenance_party_detail group by party_id,zzjb) a3 ON a1.id = a3.party_id ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id}) ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type,a3.zzjb ");

		return sb.toString();
	}

	/**
	 * 辖区内 各类 应急救援发生的次数
	 * 
	 * @param inMap
	 * @return
	 */
	public String countRescueNum(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT   ");
		sb.append(
				"a1.parent_id id,a1.parent_name party_name,a1.parent_type party_type,a2.accident_reason,IFNULL(SUM(a2.rescue_count),0) rescue_count ");
		sb.append("FROM    ");
		sb.append(
				"(SELECT a1.*,a2.party_type parent_type FROM sub_party_info a1 INNER JOIN party a2 ON a1.parent_id=a2.id) a1 ");
		sb.append(
				"LEFT JOIN (SELECT rescue_party_id,accident_reason,COUNT(1) rescue_count FROM emergency_rescue where locate(DATE_FORMAT(NOW(),'%Y'),rescue_end_time)>0 GROUP BY rescue_party_id,accident_reason) a2 ");
		sb.append("ON a1.id = a2.rescue_party_id  ");
		sb.append("WHERE a1.parent_id IN (SELECT id FROM party WHERE id = #{id} OR parent_id = #{id})   ");
		sb.append("GROUP BY a1.parent_id,a1.parent_name,a1.parent_type,a2.accident_reason ");

		return sb.toString();
	}

	/**
	 * 查询待通知的物业人员列表
	 * 
	 * @return
	 */
	public String findNotice() {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT\n" +
				"\t\ta.maintenance_user_first as safe_user_id,a.elevator_num,a.build_addr, a.outside_num, a.used_party_id,a.elevator_street,a.elevator_addr,a.elevator_model,a.id as elevatorId,\n" +
				"\t\tCASE WHEN (next_halfmon_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_halfmon_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d')) then next_halfmon_maint_date\n" +
				"      WHEN  (next_quarterly_mainte_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_quarterly_mainte_date >= DATE_FORMAT(NOW(),'%Y%m%d')) THEN next_quarterly_mainte_date\n" +
				"        WHEN (next_halfyear_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_halfyear_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d')) THEN next_halfyear_maint_date\n" +
				"        WHEN (next_year_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_year_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d')) THEN next_year_maint_date ELSE NULL END maint_date,\n" +
				"\t\t'0' AS status_id , b.register_id\n" +
				"\t\tFROM elevator a INNER JOIN user_reg_id b ON a.maintenance_user_first = b.user_id\n" +
				"    WHERE (next_halfmon_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_halfmon_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d'))\n" +
				"\t\tOR (next_quarterly_mainte_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_quarterly_mainte_date >= DATE_FORMAT(NOW(),'%Y%m%d'))\n" +
				"\t\tOR (next_halfyear_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_halfyear_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d'))\n" +
				"\t\tOR (next_year_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_year_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d'))\n" +
				"\t\tUNION\n" +
				"\n" +
				"SELECT\n" +
				"\t\ta.maintenance_user_second as safe_user_id,a.elevator_num,a.build_addr, a.outside_num, a.used_party_id,a.elevator_street,a.elevator_addr,a.elevator_model,a.id as elevatorId,\n" +
				"\t\tCASE WHEN (next_halfmon_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_halfmon_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d')) then next_halfmon_maint_date\n" +
				"      WHEN  (next_quarterly_mainte_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_quarterly_mainte_date >= DATE_FORMAT(NOW(),'%Y%m%d')) THEN next_quarterly_mainte_date\n" +
				"        WHEN (next_halfyear_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_halfyear_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d')) THEN next_halfyear_maint_date\n" +
				"        WHEN (next_year_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_year_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d')) THEN next_year_maint_date ELSE NULL END maint_date,\n" +
				"\t\t'0' AS status_id , b.register_id\n" +
				"\t\tFROM elevator a INNER JOIN user_reg_id b ON a.maintenance_user_second = b.user_id\n" +
				"    WHERE (next_halfmon_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_halfmon_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d'))\n" +
				"\t\tOR (next_quarterly_mainte_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_quarterly_mainte_date >= DATE_FORMAT(NOW(),'%Y%m%d'))\n" +
				"\t\tOR (next_halfyear_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_halfyear_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d'))\n" +
				"\t\tOR (next_year_maint_date <= DATE_FORMAT(DATE_ADD(NOW() ,INTERVAL 1 DAY),'%Y%m%d')\n" +
				"\t\tAND next_year_maint_date >= DATE_FORMAT(NOW(),'%Y%m%d'))\n" +
				"\t\tUNION\n" +
				"\n" +
				"    SELECT\n" +
				"\t\ta.safe_user_id,a.elevator_num,a.build_addr, a.outside_num, a.used_party_id,a.elevator_street,a.elevator_addr,a.elevator_model,a.id as elevatorId,\n" +
				"    CASE WHEN next_halfmon_maint_date < DATE_FORMAT(NOW(), '%Y%m%d') then next_halfmon_maint_date\n" +
				"      WHEN  (next_quarterly_mainte_date < DATE_FORMAT(NOW(), '%Y%m%d')) THEN next_quarterly_mainte_date\n" +
				"        WHEN (next_halfyear_maint_date < DATE_FORMAT(NOW(), '%Y%m%d')) THEN next_halfyear_maint_date\n" +
				"        WHEN (next_year_maint_date < DATE_FORMAT(NOW(), '%Y%m%d')) THEN next_year_maint_date ELSE NULL END maint_date,\n" +
				"\t  '1' AS status_id, b.register_id\n" +
				"\t\tFROM elevator a INNER JOIN user_reg_id b ON a.safe_user_id = b.user_id\n" +
				"    where (next_halfmon_maint_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"         OR (next_quarterly_mainte_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"               OR (next_halfyear_maint_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"                     OR (next_year_maint_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"    UNION\n" +
				"\n" +
				"    SELECT\n" +
				"\t\ta.maintenance_user_first as safe_user_id,a.elevator_num,a.build_addr, a.outside_num, a.used_party_id,a.elevator_street,a.elevator_addr,a.elevator_model,a.id as elevatorId,\n" +
				"    CASE WHEN next_halfmon_maint_date < DATE_FORMAT(NOW(), '%Y%m%d') then next_halfmon_maint_date\n" +
				"      WHEN  (next_quarterly_mainte_date < DATE_FORMAT(NOW(), '%Y%m%d')) THEN next_quarterly_mainte_date\n" +
				"        WHEN (next_halfyear_maint_date < DATE_FORMAT(NOW(), '%Y%m%d')) THEN next_halfyear_maint_date\n" +
				"        WHEN (next_year_maint_date < DATE_FORMAT(NOW(), '%Y%m%d')) THEN next_year_maint_date ELSE NULL END maint_date,\n" +
				"\t  '1' AS status_id, b.register_id\n" +
				"\t\tFROM elevator a INNER JOIN user_reg_id b ON a.maintenance_user_first = b.user_id\n" +
				"    where (next_halfmon_maint_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"         OR (next_quarterly_mainte_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"               OR (next_halfyear_maint_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"                     OR (next_year_maint_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"    UNION\n" +
				"\n" +
				"    SELECT\n" +
				"\t\ta.maintenance_user_second as safe_user_id,a.elevator_num,a.build_addr, a.outside_num, a.used_party_id,a.elevator_street,a.elevator_addr,a.elevator_model,a.id as elevatorId,\n" +
				"    CASE WHEN next_halfmon_maint_date < DATE_FORMAT(NOW(), '%Y%m%d') then next_halfmon_maint_date\n" +
				"      WHEN  (next_quarterly_mainte_date < DATE_FORMAT(NOW(), '%Y%m%d')) THEN next_quarterly_mainte_date\n" +
				"        WHEN (next_halfyear_maint_date < DATE_FORMAT(NOW(), '%Y%m%d')) THEN next_halfyear_maint_date\n" +
				"        WHEN (next_year_maint_date < DATE_FORMAT(NOW(), '%Y%m%d')) THEN next_year_maint_date ELSE NULL END maint_date,\n" +
				"\t  '1' AS status_id, b.register_id\n" +
				"\t\tFROM elevator a INNER JOIN user_reg_id b ON a.maintenance_user_second = b.user_id\n" +
				"    where (next_halfmon_maint_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"         OR (next_quarterly_mainte_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"               OR (next_halfyear_maint_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"                     OR (next_year_maint_date < DATE_FORMAT(NOW(), '%Y%m%d'))\n" +
				"    UNION\n" +
				"\t\tSELECT\n" +
				"\t\t\t\ta.safe_user_id,a.elevator_num, a.build_addr, a.outside_num,a.used_party_id,a.elevator_street,a.elevator_addr,a.elevator_model,a.id as elevatorId,\n" +
				"\t\ta.next_check_date maint_date,'3' status_id,b.register_id\n" +
				"\t\tFROM elevator a LEFT JOIN user_reg_id b ON a.safe_user_id = b.user_id\n" +
				"\t\tWHERE a.next_check_date <= DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 45 DAY),'%Y%m%d')\n" +
				"\t\tAND a.next_check_date >= DATE_FORMAT(NOW(),'%Y%m%d')\n" +
				"\t\tUNION\n" +
				"\t\tSELECT\n" +
				"\t\ta.safe_user_id,a.elevator_num, a.build_addr, a.outside_num,a.used_party_id,a.elevator_street,a.elevator_addr,a.elevator_model,a.id as elevatorId,\n" +
				"\t\ta.next_check_date maint_date,'4' status_id,b.register_id\n" +
				"\t\tFROM elevator a LEFT JOIN user_reg_id b ON a.safe_user_id = b.user_id\n" +
				"\t\tWHERE a.next_check_date < DATE_FORMAT(NOW(),'%Y%m%d')\n" +
				"\t\tUNION\n" +
				"\t\tSELECT\n" +
				"\t\ta.maintenance_user_first,a.elevator_num,a.build_addr, a.outside_num, a.maintenance_party_id,a.elevator_street,a.elevator_addr,a.elevator_model,a.id as elevatorId,\n" +
				"\t\ta.next_check_date maint_date,'4' status_id,b.register_id\n" +
				"\t\tFROM elevator a LEFT JOIN user_reg_id b ON a.safe_user_id = b.user_id\n" +
				"\t\tWHERE a.next_check_date < DATE_FORMAT(NOW(),'%Y%m%d')\n" +
				"\t\tUNION\n" +
				"\t\tSELECT\n" +
				"\t\ta.maintenance_user_second,a.elevator_num,a.build_addr, a.outside_num, a.maintenance_party_id,a.elevator_street,a.elevator_addr,a.elevator_model,a.id as elevatorId,\n" +
				"\t\ta.next_check_date maint_date,'4' status_id,b.register_id\n" +
				"\t\tFROM elevator a LEFT JOIN user_reg_id b ON a.safe_user_id = b.user_id\n" +
				"\t\tWHERE a.next_check_date < DATE_FORMAT(NOW(),'%Y%m%d')");
		return sb.toString();
	}

	/**
	 * 查询电梯，投诉，维修，维保，应急救援，救援演练 年度总数 使用单位
	 * 
	 * @return
	 */
	public String findCountCount(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select b.*,d.*,e.*,f.*,g.*,h.* from ");
		sb.append(" (select count(*) as elevatorCount from elevator as a where a.used_party_id = #{id} ) b , ");
		sb.append(" (select count(*) as resoueDrilCount from rescue_drill where YEAR(drill_date) = YEAR(NOW())) d , ");
		sb.append(
				" (select count(*) as repairInfoCount from repair_info where elevator_num in ( select a.elevator_num from elevator as a where a.used_party_id = #{id}) and YEAR(report_date) = YEAR(NOW())) e, ");
		sb.append(
				" (select count(*) as emergencyRescueCount from emergency_rescue where elevator_num in ( select a.elevator_num from elevator as a where a.used_party_id = #{id}) and YEAR(rescue_end_time) = YEAR(NOW())) f, ");
		sb.append(
				" (select count(*) as appraisalCount from elevator_appraisal where elevator_id in ( select a.id from elevator as a where a.used_party_id = #{id}) and YEAR(appraisal_time) = YEAR(NOW()) and evaluate_type = 2) g, ");
		sb.append(
				" (select IFNULL(a.debrisHalfMon,0)+IFNULL(b.escalatorHalfMon,0)+IFNULL(c.hydpressHalfMon,0)+IFNULL(d.tractionHalfMon,0) as maintNum from ( ");
		sb.append(
				"  select count(*) as debrisHalfMon from debris_half_mon where elevator_num in ( select a.elevator_num from elevator as a where a.used_party_id = #{id}) and YEAR(maintenance_start_date) = YEAR(NOW())) a, ");
		sb.append(
				"( select count(*) as escalatorHalfMon from escalator_half_mon where elevator_num in ( select a.elevator_num from elevator as a where a.used_party_id = #{id}) and YEAR(maintenance_start_date) = YEAR(NOW())) b,");
		sb.append(
				" (select count(*) as hydpressHalfMon from hydpress_half_mon where elevator_num in ( select a.elevator_num from elevator as a where a.used_party_id = #{id}) and YEAR(maintenance_start_date) = YEAR(NOW())) c,");
		sb.append(
				" (select count(*) as tractionHalfMon from traction_half_mon  where elevator_num in ( select a.elevator_num from elevator as a where a.used_party_id = #{id}) and YEAR(maintenance_start_date) = YEAR(NOW())) d ) h ");
		return sb.toString();
	}

	/**
	 * 查询电梯，维修，维保 年度总数 维保
	 *
	 * @return
	 */
	public String findMaintCount(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select b.* ,e.*,h.* from ");
		sb.append(" (select count(*) as elevatorCount from elevator as a where a.maintenance_party_id = #{id} ) b , ");
		sb.append(
				" (select count(*) as repairInfoCount from repair_info where repair_party_id = #{id} and YEAR(report_date) = YEAR(NOW())) e, ");
		sb.append(
				" (select IFNULL(a.debrisHalfMon,0)+IFNULL(b.escalatorHalfMon,0)+IFNULL(c.hydpressHalfMon,0)+IFNULL(d.tractionHalfMon,0) as maintNum from ( ");
		sb.append(
				"  select count(*) as debrisHalfMon from debris_half_mon where maintenance_party_id = #{id} and YEAR(maintenance_start_date) = YEAR(NOW())) a, ");
		sb.append(
				"( select count(*) as escalatorHalfMon from escalator_half_mon where maintenance_party_id = #{id} and YEAR(maintenance_start_date) = YEAR(NOW())) b,");
		sb.append(
				" (select count(*) as hydpressHalfMon from hydpress_half_mon where maintenance_party_id = #{id} and YEAR(maintenance_start_date) = YEAR(NOW())) c,");
		sb.append(
				" (select count(*) as tractionHalfMon from traction_half_mon  where maintenance_party_id = #{id} and YEAR(maintenance_start_date) = YEAR(NOW())) d ) h ");
		return sb.toString();
	}

	/**
	 * 查询 维保单位今日维保 详细
	 * 
	 * @param inMap
	 * @return
	 */
	public String findTodayMaint(Map inMap) {

		StringBuffer sb = new StringBuffer();
		sb.append(
				"( select a.id, b.elevator_type_id as elevatorTypeId, a.type, a.elevator_num as elevatorNum, c.party_name as partyName, " +
						"b.elevator_addr as elevatorAddr, d.type_name as elevatorType, a.maintenance_start_date as maintenanceStartDate, " +
						"a.maintenance_end_date as maintenanceEndDate, a.maintenance_user_name as maintenanceUserName, a.maintenance_user_name2 as maintenanceUserName2 "
						+ " from debris_half_mon a " + " left join elevator b on b.elevator_num = a.elevator_num "
						+ " left join party c on c.id = b.used_party_id "
						+ " left join elevator_type d on d.id=b.elevator_type_id where to_days(a.maintenance_start_date) = to_days(now()) "
						+ " and a.maintenance_party_id = #{id} )");
		sb.append(" UNION ALL ");
		sb.append(
				"( select a.id, b.elevator_type_id as elevatorTypeId, a.type, a.elevator_num as elevatorNum, c.party_name as partyName," +
						" b.elevator_addr as elevatorAddr, d.type_name as elevatorType, a.maintenance_start_date as maintenanceStartDate, " +
						"a.maintenance_end_date as maintenanceEndDate, a.maintenance_user_name as maintenanceUserName , a.maintenance_user_name2 as maintenanceUserName2 "
						+ " from hydpress_half_mon a " + " left join elevator b on b.elevator_num = a.elevator_num "
						+ " left join party c on c.id = b.used_party_id "
						+ " left join elevator_type d on d.id=b.elevator_type_id where to_days(a.maintenance_start_date) = to_days(now()) "
						+ " and a.maintenance_party_id = #{id} )");
		sb.append(" UNION ALL ");
		sb.append(
				"( select a.id, b.elevator_type_id as elevatorTypeId, a.type, a.elevator_num as elevatorNum, c.party_name as partyName, " +
						"b.elevator_addr as elevatorAddr, d.type_name as elevatorType, a.maintenance_start_date as maintenanceStartDate, " +
						"a.maintenance_end_date as maintenanceEndDate, a.maintenance_user_name as maintenanceUserName , a.maintenance_user_name2 as maintenanceUserName2 "
						+ " from escalator_half_mon a " + " left join elevator b on b.elevator_num = a.elevator_num "
						+ " left join party c on c.id = b.used_party_id "
						+ " left join elevator_type d on d.id=b.elevator_type_id where to_days(a.maintenance_start_date) = to_days(now()) "
						+ " and a.maintenance_party_id = #{id} )");

		sb.append(" UNION ALL ");
		sb.append(
				"( select a.id, b.elevator_type_id as elevatorTypeId, a.type, a.elevator_num as elevatorNum, c.party_name as partyName, " +
						"b.elevator_addr as elevatorAddr, d.type_name as elevatorType, a.maintenance_start_date as maintenanceStartDate," +
						" a.maintenance_end_date as maintenanceEndDate, a.maintenance_user_name as maintenanceUserName , a.maintenance_user_name2 as maintenanceUserName2 "
						+ " from traction_half_mon a " + " left join elevator b on b.elevator_num = a.elevator_num "
						+ " left join party c on c.id = b.used_party_id "
						+ " left join elevator_type d on d.id=b.elevator_type_id where to_days(a.maintenance_start_date) = to_days(now()) "
						+ " and a.maintenance_party_id = #{id} )");

		return sb.toString();

	}

	/**
	 * 通过市角色获取信息
	 */
	public String countEleNumCity(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select * from ( ");
		sb.append(" (select count(1) elevatorNum from elevator r where r.branch_office in (select id from party where parent_id in (select id from party where parent_id=#{id})) and r.is_stop is not null) a, ");
		sb.append(" (select count(distinct  BRAND_NAME) brandNum  from elevator b where b.branch_office in (select id from party where parent_id in (select id from party where parent_id=#{id})) and b.is_stop is not null and b.BRAND_NAME is not null) b, ");
		sb.append(" (SELECT count(1) operatorNum FROM SYS_USER_ROLE WHERE  user_id in (select user_id from party_user where party_id in (select id from party where parent_id in (select id from party where parent_id=#{id})) ) and  (ROLE_ID = '5' OR ROLE_ID = '6' OR ROLE_ID = '7' OR ROLE_ID = '8')) c, ");
		sb.append(" (select count(1) usedPartyNum from party where parent_id in (select id from party where parent_id=#{id}) and party_type='2') d, ");
		sb.append(" (select count(1) maintPartyNum from party where parent_id in (select id from party where parent_id=#{id}) and party_type='1') e, ");
		sb.append(" (SELECT count(1) contractNum FROM PARTY_CONTRACT INNER JOIN mysql.help_topic on 1=1 where help_topic_id < LENGTH(contract_name)-LENGTH(REPLACE(contract_name,'|',''))+1 and  party_id in (select id from party where parent_id in (select id from party where parent_id=#{id})) and contract_name is not null) f ");
		sb.append(" ) ");
		return sb.toString();
	}

	/**
	 * 通过局角色获取信息
	 */
	public String countEleNumArea(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select * from ( ");
		sb.append(" (select count(1) elevatorNum from elevator r where r.branch_office in ((select id from party where parent_id=#{id} and party_type='4' )) and r.is_stop is not null) a, ");
		sb.append(" (select count(distinct  BRAND_NAME) brandNum  from elevator b where b.branch_office in ( (select id from party where parent_id=#{id} and party_type='4' )) and b.is_stop is not null and b.BRAND_NAME is not null) b, ");
		sb.append(" (SELECT count(1) operatorNum FROM SYS_USER_ROLE WHERE  user_id in (select user_id from party_user where party_id in ((select id from party where parent_id=#{id})) ) and  (ROLE_ID = '5' OR ROLE_ID = '6' OR ROLE_ID = '7' OR ROLE_ID = '8')) c, ");
		sb.append(" (select count(1) usedPartyNum from party where parent_id =#{id} and party_type='2') d, ");
		sb.append(" (select count(1) maintPartyNum from party where parent_id =#{id} and party_type='1') e, ");
		sb.append(" (SELECT count(1) contractNum FROM PARTY_CONTRACT INNER JOIN mysql.help_topic on 1=1 where  help_topic_id < LENGTH(contract_name)-LENGTH(REPLACE(contract_name,'|',''))+1 and  party_id in ((select id from party where parent_id=#{id})) and contract_name is not null) f ");
		sb.append(" ) ");
		return sb.toString();
	}

    /**
     * 市辖区内电梯年限分布
     *
     * @return
     */
    public String countYearDistribCity(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append(
                "SELECT SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 < 10 THEN 1 ELSE 0 END) lessTen,");
        sb.append(
                "SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 >= 10 AND (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 < 15 THEN 1 ELSE 0 END) lessFifteen,");
        sb.append(
                "SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 >= 15 AND (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 < 20 THEN 1 ELSE 0 END) lessTwenty,");
        sb.append(
                "SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 >= 20 THEN 1 ELSE 0 END) gatherTwenty FROM ELEVATOR  where branch_office in (select id from party where parent_id in (select id from party where parent_id=#{id})) and is_stop is not null ");
        return sb.toString();
    }

    /**
     * 局辖区内电梯年限分布
     *
     * @return
     */
    public String countYearDistribArea(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append(
                "SELECT SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 < 10 THEN 1 ELSE 0 END) lessTen,");
        sb.append(
                "SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 >= 10 AND (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 < 15 THEN 1 ELSE 0 END) lessFifteen,");
        sb.append(
                "SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 >= 15 AND (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 < 20 THEN 1 ELSE 0 END) lessTwenty,");
        sb.append(
                "SUM(CASE WHEN (DATE_FORMAT(NOW(),'%Y%m') - SUBSTRING(elevator_num,11,6))/100 >= 20 THEN 1 ELSE 0 END) gatherTwenty FROM ELEVATOR where branch_office in ((select id from party where parent_id=#{id})) and is_stop is not null ");
        return sb.toString();
    }

	/**
	 * 市辖区内保障力量分布
	 *
	 * @return
	 */
	public String countOperDistribCity(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT SUM(CASE WHEN ROLE_ID = 3 THEN 1 ELSE 0 END) watcherNum,");
		sb.append("SUM(CASE WHEN ROLE_ID = 5 THEN 1 ELSE 0 END) saferNum,");
		sb.append("SUM(CASE WHEN ROLE_ID = 7 THEN 1 ELSE 0 END) mainterNum ");
		sb.append("FROM SYS_USER_ROLE where user_id in (select user_id from party_user where party_id in (select id from party where parent_id in (select id from party where parent_id=#{id}))) ");
		return sb.toString();
	}

	/**
	 * 局、分局辖区内保障力量分布
	 *
	 * @return
	 */
	public String countOperDistribArea(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT SUM(CASE WHEN ROLE_ID = 3 THEN 1 ELSE 0 END) watcherNum,");
		sb.append("SUM(CASE WHEN ROLE_ID = 5 THEN 1 ELSE 0 END) saferNum,");
		sb.append("SUM(CASE WHEN ROLE_ID = 7 THEN 1 ELSE 0 END) mainterNum ");
		sb.append("FROM SYS_USER_ROLE where user_id in (select user_id from party_user where party_id in ((select id from party where parent_id=#{id}))) ");
		return sb.toString();
	}

	/**
	 * 市 电梯事故隐患排除情况
	 *
	 * @param
	 * @return
	 */
	public String countRepairNumCity(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select a.* from ( ");
		sb.append("SELECT A.ID id,A.party_type partyType,A.PARTY_NAME partyName,A.last_manage_party lastManageParty,IFNULL(SUM(C.NUM),0) dayRepairNum,");
		sb.append("IFNULL(SUM(D.NUM),0) quarterRepairNum,IFNULL(SUM(E.NUM),0) yearRepairNum FROM ");
		sb.append("PARTY A LEFT JOIN SUB_PARTY_INFO B ON A.ID = B.PARENT_ID ");
		sb.append("LEFT JOIN (SELECT COUNT(1) NUM,REPAIR_PARTY_ID FROM REPAIR_INFO WHERE RESOLVE_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%') GROUP BY REPAIR_PARTY_ID) C ON B.ID = C.REPAIR_PARTY_ID ");
		sb.append("LEFT JOIN (SELECT COUNT(1) NUM,REPAIR_PARTY_ID FROM REPAIR_INFO WHERE QUARTER(RESOLVE_DATE) = QUARTER(NOW()) AND YEAR(RESOLVE_DATE) = YEAR(NOW()) GROUP BY REPAIR_PARTY_ID) D ON B.ID = D.REPAIR_PARTY_ID ");
		sb.append("LEFT JOIN (SELECT COUNT(1) NUM,REPAIR_PARTY_ID FROM REPAIR_INFO WHERE YEAR(RESOLVE_DATE) = YEAR(NOW()) GROUP BY REPAIR_PARTY_ID) E ON B.ID = E.REPAIR_PARTY_ID ");
		sb.append("GROUP BY A.ID,A.PARTY_NAME,A.last_manage_party ");
		sb.append(" ) a where a.partyType='4' and a.id in (select id from party where id in (select id from party where parent_id=#{id})) ");
		return sb.toString();
	}

	/**
	 * 区 电梯事故隐患排除情况
	 *
	 * @param
	 * @return
	 */
	public String countRepairNumArea(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select p.party_name partyName,IFNULL(daytab.dayRepairNum,0) dayRepairNum,IFNULL(yeartab.yearRepairNum,0) yearRepairNum,IFNULL(quartertab.quarterRepairNum,0) quarterRepairNum from party p ");
		sb.append("left join (select sum(1) yearRepairNum ,ele.branch_office from REPAIR_INFO info left join elevator ele ON info.elevator_id = ele.id where YEAR(RESOLVE_DATE) = YEAR(NOW()) and ele.branch_office is not null ");
		sb.append("group by ele.branch_office) yeartab ON p.id = yeartab.branch_office ");
		sb.append("left join (select sum(1) quarterRepairNum ,ele.branch_office from REPAIR_INFO info left join elevator ele ON info.elevator_id = ele.id where QUARTER(RESOLVE_DATE) = QUARTER(NOW()) AND YEAR(RESOLVE_DATE) = YEAR(NOW()) and ele.branch_office is not null ");
		sb.append("group by ele.branch_office ) quartertab ON p.id = quartertab.branch_office ");
		sb.append("left join (select sum(1) dayRepairNum ,ele.branch_office from REPAIR_INFO info left join elevator ele ON info.elevator_id = ele.id where RESOLVE_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%') and ele.branch_office is not null ");
		sb.append("group by ele.branch_office ) daytab ON p.id = daytab.branch_office ");
		sb.append("where parent_id=" + id + " and party_type='4' ");
		return sb.toString();
	}

	/**
	 * 区 电梯安全保障工作超期情况
	 *
	 * @param
	 * @return
	 */
	public String countMaintNumArea(Map inMap) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT A.ID   id, ");
		sb.append("A.PARTY_NAME   partyName, ");
		sb.append("IFNULL(c.NUM,0) yearMaintNum, ");
		sb.append("IFNULL(b.NUM,0) quarterMaintNum, ");
		sb.append("IFNULL(aa.NUM,0) dayMaintNum, ");
		sb.append("IFNULL(H.NUM,0) dayCheckNum, ");
		sb.append("IFNULL(G.NUM,0) quarterCheckNum, ");
		sb.append("IFNULL(F.NUM,0) yearCheckNum ");

		sb.append("FROM PARTY A ");
		sb.append("LEFT JOIN (select branch_office,IFNULL(SUM(num), 0) NUM from (\n" +
				"                        SELECT  sum(1) num ,ele.branch_office FROM DEBRIS_HALF_MON dhm\n" +
				"                        left join elevator ele ON dhm.elevator_num = ele.elevator_num\n" +
				"                        WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    union ALL\n" +
				"                        SELECT  sum(1) num  ,ele.branch_office FROM ESCALATOR_HALF_MON ehm\n" +
				"                        left join elevator ele ON ehm.elevator_num = ele.elevator_num\n" +
				"                        WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    union ALL\n" +
				"                        SELECT  sum(1) num ,ele.branch_office FROM HYDPRESS_HALF_MON hhm\n" +
				"                        left join elevator ele ON hhm.elevator_num = ele.elevator_num\n" +
				"                        WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    union ALL\n" +
				"                        SELECT  sum(1) num ,ele.branch_office FROM TRACTION_HALF_MON thm\n" +
				"                        left join elevator ele ON thm.elevator_num = ele.elevator_num\n" +
				"                        WHERE YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    ) dayT group by dayT.branch_office) C ON c.branch_office = A.id ");
		sb.append("LEFT JOIN (select branch_office,IFNULL(SUM(num), 0) NUM from (\n" +
				"                        SELECT  sum(1) num ,ele.branch_office FROM DEBRIS_HALF_MON dhm\n" +
				"                        left join elevator ele ON dhm.elevator_num = ele.elevator_num\n" +
				"                        WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND\n" +
				"                                   YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    union ALL\n" +
				"                        SELECT  sum(1) num  ,ele.branch_office FROM ESCALATOR_HALF_MON ehm\n" +
				"                        left join elevator ele ON ehm.elevator_num = ele.elevator_num\n" +
				"                        WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND\n" +
				"                                   YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    union ALL\n" +
				"                        SELECT  sum(1) num ,ele.branch_office FROM HYDPRESS_HALF_MON hhm\n" +
				"                        left join elevator ele ON hhm.elevator_num = ele.elevator_num\n" +
				"                        WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND\n" +
				"                                   YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    union ALL\n" +
				"                        SELECT  sum(1) num ,ele.branch_office FROM TRACTION_HALF_MON thm\n" +
				"                        left join elevator ele ON thm.elevator_num = ele.elevator_num\n" +
				"                        WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(NOW()) AND\n" +
				"                                   YEAR(MAINTENANCE_END_DATE) = YEAR(NOW())\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    ) dayT group by dayT.branch_office) b ON b.branch_office = A.id ");
		sb.append("LEFT JOIN (select branch_office,IFNULL(SUM(num), 0) NUM from (\n" +
				"                        SELECT  sum(1) num ,ele.branch_office FROM DEBRIS_HALF_MON dhm\n" +
				"                        left join elevator ele ON dhm.elevator_num = ele.elevator_num\n" +
				"                        WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    union ALL\n" +
				"                        SELECT  sum(1) num  ,ele.branch_office FROM ESCALATOR_HALF_MON ehm\n" +
				"                        left join elevator ele ON ehm.elevator_num = ele.elevator_num\n" +
				"                        WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    union ALL\n" +
				"                        SELECT  sum(1) num ,ele.branch_office FROM HYDPRESS_HALF_MON hhm\n" +
				"                        left join elevator ele ON hhm.elevator_num = ele.elevator_num\n" +
				"                        WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    union ALL\n" +
				"                        SELECT  sum(1) num ,ele.branch_office FROM TRACTION_HALF_MON thm\n" +
				"                        left join elevator ele ON thm.elevator_num = ele.elevator_num\n" +
				"                        WHERE MAINTENANCE_END_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%')\n" +
				"                        AND IS_IN_TIME_MAINT = '2' and ele.branch_office is not null\n" +
				"                        group by ele.branch_office\n" +
				"                    ) dayT group by dayT.branch_office) aa ON aa.branch_office = A.id ");
		sb.append("LEFT JOIN (SELECT sum(1) num,B.branch_office FROM ELEVATOR_OVERTIME A\n" +
				"                        LEFT JOIN ELEVATOR B ON A.ELEVATOR_ID = B.ID\n" +
				"                        WHERE YEAR(A.OVERTIME_DATE) = YEAR(NOW()) and b.branch_office is not null\n" +
				"                        GROUP BY B.branch_office) F ON A.ID = F.branch_office ");
		sb.append("LEFT JOIN (SELECT sum(1) num,B.branch_office FROM ELEVATOR_OVERTIME A\n" +
				"                        LEFT JOIN ELEVATOR B ON A.ELEVATOR_ID = B.ID\n" +
				"                        WHERE QUARTER(A.OVERTIME_DATE) = QUARTER(NOW()) AND YEAR(A.OVERTIME_DATE) = YEAR(NOW()) and b.branch_office is not null\n" +
				"                        GROUP BY B.branch_office) G ON A.ID = G.branch_office ");
		sb.append("LEFT JOIN (SELECT sum(1) num,B.branch_office FROM ELEVATOR_OVERTIME A\n" +
				"                        LEFT JOIN ELEVATOR B ON A.ELEVATOR_ID = B.ID\n" +
				"                        WHERE A.OVERTIME_DATE LIKE CONCAT(DATE_FORMAT(NOW(),'%Y%m%d'),'%') and b.branch_office is not null\n" +
				"                        GROUP BY B.branch_office) H ON A.ID = H.branch_office ");
		sb.append("where parent_id=#{id} and party_type='4' ");
		return sb.toString();
	}


	/**
	 * 自定义一些查询
	 *
	 * @return
	 */
	public String selfCount(String sql) {
		return sql;
	}
}
